package com.info.zhiduoduo.repository.mapper.partner;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.partner.PartnerMakerDetailDto;
import com.info.zhiduoduo.repository.dto.partner.PartnerMakerDto;
import com.info.zhiduoduo.repository.dto.partner.PartnerMakerExpireDto;
import com.info.zhiduoduo.repository.dto.partner.PartnerMakerInfoDto;
import com.info.zhiduoduo.repository.entity.partner.PartnerCompany;
import com.info.zhiduoduo.repository.entity.partner.PartnerMaker;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * <p>
 * 创客合伙人信息，保存佣金比例等信息 Mapper 接口
 * </p>
 *
 * @author bob
 * @since 2021-09-12
 */
public interface PartnerMakerMapper extends BaseMapper<PartnerMaker> {

    @Select("select COUNT(*) from zdd_partner_maker where partnerid = #{partnerid} and userid = #{userid} and type = #{type} and deleted = 0")
    boolean existsByPartneridAndUserid(int partnerid, int userid, int type);

    @Select(" select pm.* from zdd_partner_maker pm " +
            " where pm.userid = #{userid} " +
            " and if(#{type}=0, 1=1, pm.type=#{type}) " +
            " and pm.state = 1 " +
            " and pm.deleted = 0 ")
    List<PartnerMaker> getValidList(@Param("userid") int userid, @Param("type") int type);

    @Select(" select pm.userid,ms.username,pm.state " +
            " from zdd_partner_maker pm " +
            " join zdd_maker_studio ms on pm.userid=ms.userid " +
            " where pm.partnerid=#{partnerid} group by pm.partnerid,pm.userid order by pm.id ")
    List<PartnerMakerInfoDto> getPartnerMakerBrokerageInfoByPartnerId(@Param("partnerid") int partnerid);

    @Select(" select * from zdd_partner_maker " +
            " where partnerid = #{partnerid} " +
            " and deleted = 0 ")
    List<PartnerMaker> getPartnerMakerByPartnerId(@Param("partnerid") int partnerid);

    @Select(" select * from zdd_partner_maker " +
            " where userid = #{userid} " +
            " and deleted = 0 ")
    List<PartnerMaker> getPartnerMakerByuserid(@Param("userid") int userid);

    @Select(" select COUNT(DISTINCT userid) from zdd_partner_maker " +
            " where partnerid = #{partnerid} " +
            " and DATE(createtime) between #{start} and #{end} " +
            " and deleted = 0 ")
    int countPartnerMakerByDate(@Param("partnerid") int partnerid, @Param("start")Date start, @Param("end") Date end);

    @Select(" select COUNT(DISTINCT userid) from zdd_partner_maker " +
            " where partnerid = #{partnerid} " +
            " and YEAR(createtime) = YEAR(#{month}) " +
            " and QUARTER(createtime) = QUARTER(#{month}) " +
            " and deleted = 0 ")
    int countPartnerMakerByQuarter(@Param("partnerid") int partnerid, @Param("month")Date month);


    @Select(" select DISTINCT userid from zdd_partner_maker " +
            " where partnerid = #{partnerid} " +
            " and deleted = 0 ")
    List<Integer> getPartnerMakerIds(@Param("partnerid") int partnerid);


    @Select(" select DISTINCT userid from zdd_partner_maker " +
            " where partnerid = #{partnerid} " +
            " and expiredate >= CURDATE() " +
            " and DATE(createtime) between #{start} and #{end} " +
            " and deleted = 0 ")
    List<Integer> getPartnerMakerIdsByDate(@Param("partnerid") int partnerid, @Param("start")Date start, @Param("end") Date end);

    @Select(" select pm.id,pa.partnerid,pa.realname,pm.userid,ms.username,ma.realname as makerRealname,p.phone,pa.idfrontimg,pa.idbackimg," +
            " pm.type,pm.settle_type,pm.ratio,pm.expiredate,pm.state,pm.createtime,bm.business_manager as partnerBusinessManager," +
            " pa.audit_pass_time as shareBenefitDate" +
            " from zdd_partner_maker pm " +
            " join zdd_partner p on p.partnerid = pm.partnerid " +
            " join zdd_partner_account pa on pa.partnerid = p.partnerid " +
            " join zdd_maker_studio ms on ms.userid = pm.userid " +
            " join zdd_maker_account ma  on ma.userid = pm.userid " +
            " left join zdd_business_manager bm on bm.business_manager_id = pa.business_manager_id " +
            " where pm.expiredate between CURDATE() and DATE(#{date}) " +
            " and pm.deleted = 0 " +
            " order by pm.expiredate desc ")
    List<PartnerMakerExpireDto> listPendingExpired(Date date);

    @Select(" select pm.id,pa.partnerid,pa.realname,pm.userid,ms.username,p.phone,pa.idfrontimg,pa.idbackimg," +
            " pm.type,pm.settle_type,pm.ratio,pm.expiredate,pm.state,pm.createtime,pa.audit_pass_time as shareBenefitDate,pm.deleted " +
            " from zdd_partner_maker pm " +
            " join zdd_partner p on p.partnerid = pm.partnerid " +
            " join zdd_partner_account pa on pa.partnerid = p.partnerid " +
            " join zdd_maker_studio ms on ms.userid = pm.userid " +
            " where IF(ISNULL(#{from}), 1=1, pm.expiredate >= DATE(#{from})) " +
            " AND IF(ISNULL(#{to}), 1=1, pm.expiredate <= DATE(#{to})) " +
            " AND IF(#{state} = 0, 1 = 1, pm.state = #{state}) " +
            " AND CONCAT(pa.realname, ms.username) like '%${name}%' " +
            " AND CASE WHEN #{expired}=1 THEN pm.expiredate > CURDATE() " +
            " WHEN #{expired}=2 THEN pm.expiredate <= CURDATE() " +
            " ELSE 1=1 " +
            " END " +
            " AND IF(#{state} = 2, 1 = 1, pm.deleted = 0) " +
            " order by pm.expiredate desc ")
    List<PartnerMakerExpireDto> listExpired(String name, int state, int expired, Date from, Date to);

    @Select(" select ms.userid,ms.createtime,ms.username,ma.realname,m.phone,GROUP_CONCAT(pa.realname) as partners," +
            " SUM(if(pm.type = 1, pm.ratio, 0)) as basicServiceCostTotal, " +
            " SUM(if(pm.type = 2, pm.ratio, 0)) as projectServiceCostTotal, " +
            " SUM(if(pm.type = 3, pm.ratio, 0)) as crowdsourceServiceCostTotal " +
            " from zdd_maker_studio ms " +
            " join zdd_maker_account ma on ma.userid = ms.userid " +
            " join zdd_maker m on m.userid = ms.userid " +
            " left join zdd_partner_maker pm on pm.userid = ms.userid and pm.state = 1 and pm.deleted = 0 " +
            " left join zdd_partner_account pa on pa.partnerid = pm.partnerid " +
            " where ms.state = 2 " +
            " and ms.createtime between #{startDate} and #{endDate} " +
            " and ((m.maker_type = 2) or (m.maker_type = 1 and  ms.taxer = 1))" +
            " group by ms.userid " +
            " having CONCAT(IFNULL(ms.username, ''), IFNULL(ma.realname, ''), IFNULL(partners,'')) like '%${name}%' " +
            " order by ms.createtime desc ")
    List<PartnerMakerDetailDto> listValidMakers(String name, Date startDate, Date endDate);

    @Select(" select pm.*,zp.phone,pa.audit_pass_time as shareBenefitDate" +
            " from zdd_partner_maker pm " +
            " join zdd_partner zp on zp.partnerid = pm.partnerid " +
            " join zdd_partner_account pa on pa.partnerid = pm.partnerid" +
            " where pm.userid = #{userid} and pm.state = 1  and pm.deleted = 0 ")
    List<PartnerMakerDto> listByUserid(int userid);


    @Select(" UPDATE zdd_partner_maker  " +
            " SET `state`=#{state}, deleted = #{deleted} " +
            " WHERE id= #{id} ")
    void updatePartnerMaker(@Param("id") int id,
                            @Param("state") int state,
                            @Param("deleted") int deleted);
}
